﻿/**  
* Ver    变更日期             负责人  变更内容
* V0.01  2015/7/9 9:13:40   何泉水    初版
*/
using System;
using System.Data;
using System.Text;
using System.Data.SQLite;
namespace He.DAL
{
	/// <summary>
	/// 数据访问类:member
	/// </summary>
	public partial class member
	{
		public member()
		{}
		#region  BasicMethod

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQLite.GetMaxID("id", "he_member"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from he_member");
			strSql.Append(" where id=@id");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@id", DbType.Int32,4)
			};
			parameters[0].Value = id;

			return DbHelperSQLite.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(He.Model.member model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into he_member(");
			strSql.Append("rank_id,user_name,user_pwd,figure_url,nickname,sex,birthday,tel,phone,qq,email,province,city,area,address,user_type,user_level,qq_openid,points,status,last_time,this_time,last_ip,this_ip,reg_time,reset_key,reset_expires,remark,attr_str1,attr_str2,attr_num1)");
			strSql.Append(" values (");
			strSql.Append("@rank_id,@user_name,@user_pwd,@figure_url,@nickname,@sex,@birthday,@tel,@phone,@qq,@email,@province,@city,@area,@address,@user_type,@user_level,@qq_openid,@points,@status,@last_time,@this_time,@last_ip,@this_ip,@reg_time,@reset_key,@reset_expires,@remark,@attr_str1,@attr_str2,@attr_num1)");
			strSql.Append(";select LAST_INSERT_ROWID()");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@rank_id", DbType.Int32,8),
					new SQLiteParameter("@user_name", DbType.String),
					new SQLiteParameter("@user_pwd", DbType.String),
					new SQLiteParameter("@figure_url", DbType.String),
					new SQLiteParameter("@nickname", DbType.String),
					new SQLiteParameter("@sex", DbType.String),
					new SQLiteParameter("@birthday", DbType.String),
					new SQLiteParameter("@tel", DbType.String),
					new SQLiteParameter("@phone", DbType.String),
					new SQLiteParameter("@qq", DbType.String),
					new SQLiteParameter("@email", DbType.String),
					new SQLiteParameter("@province", DbType.String),
					new SQLiteParameter("@city", DbType.String),
					new SQLiteParameter("@area", DbType.String),
					new SQLiteParameter("@address", DbType.String),
					new SQLiteParameter("@user_type", DbType.Int32,8),
					new SQLiteParameter("@user_level", DbType.Int32,8),
					new SQLiteParameter("@qq_openid", DbType.String),
					new SQLiteParameter("@points", DbType.Int32,8),
					new SQLiteParameter("@status", DbType.Int32,8),
					new SQLiteParameter("@last_time", DbType.String),
					new SQLiteParameter("@this_time", DbType.String),
					new SQLiteParameter("@last_ip", DbType.String),
					new SQLiteParameter("@this_ip", DbType.String),
					new SQLiteParameter("@reg_time", DbType.String),
					new SQLiteParameter("@reset_key", DbType.String),
					new SQLiteParameter("@reset_expires", DbType.String),
					new SQLiteParameter("@remark", DbType.String),
					new SQLiteParameter("@attr_str1", DbType.String),
					new SQLiteParameter("@attr_str2", DbType.String),
					new SQLiteParameter("@attr_num1", DbType.Int32,8)};
			parameters[0].Value = model.rank_id;
			parameters[1].Value = model.user_name;
			parameters[2].Value = model.user_pwd;
			parameters[3].Value = model.figure_url;
			parameters[4].Value = model.nickname;
			parameters[5].Value = model.sex;
			parameters[6].Value = model.birthday;
			parameters[7].Value = model.tel;
			parameters[8].Value = model.phone;
			parameters[9].Value = model.qq;
			parameters[10].Value = model.email;
			parameters[11].Value = model.province;
			parameters[12].Value = model.city;
			parameters[13].Value = model.area;
			parameters[14].Value = model.address;
			parameters[15].Value = model.user_type;
			parameters[16].Value = model.user_level;
			parameters[17].Value = model.qq_openid;
			parameters[18].Value = model.points;
			parameters[19].Value = model.status;
			parameters[20].Value = model.last_time;
			parameters[21].Value = model.this_time;
			parameters[22].Value = model.last_ip;
			parameters[23].Value = model.this_ip;
			parameters[24].Value = model.reg_time;
			parameters[25].Value = model.reset_key;
			parameters[26].Value = model.reset_expires;
			parameters[27].Value = model.remark;
			parameters[28].Value = model.attr_str1;
			parameters[29].Value = model.attr_str2;
			parameters[30].Value = model.attr_num1;

			object obj = DbHelperSQLite.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(He.Model.member model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update he_member set ");
			strSql.Append("rank_id=@rank_id,");
			strSql.Append("user_name=@user_name,");
			strSql.Append("user_pwd=@user_pwd,");
			strSql.Append("figure_url=@figure_url,");
			strSql.Append("nickname=@nickname,");
			strSql.Append("sex=@sex,");
			strSql.Append("birthday=@birthday,");
			strSql.Append("tel=@tel,");
			strSql.Append("phone=@phone,");
			strSql.Append("qq=@qq,");
			strSql.Append("email=@email,");
			strSql.Append("province=@province,");
			strSql.Append("city=@city,");
			strSql.Append("area=@area,");
			strSql.Append("address=@address,");
			strSql.Append("user_type=@user_type,");
			strSql.Append("user_level=@user_level,");
			strSql.Append("qq_openid=@qq_openid,");
			strSql.Append("points=@points,");
			strSql.Append("status=@status,");
			strSql.Append("last_time=@last_time,");
			strSql.Append("this_time=@this_time,");
			strSql.Append("last_ip=@last_ip,");
			strSql.Append("this_ip=@this_ip,");
			strSql.Append("reg_time=@reg_time,");
			strSql.Append("reset_key=@reset_key,");
			strSql.Append("reset_expires=@reset_expires,");
			strSql.Append("remark=@remark,");
			strSql.Append("attr_str1=@attr_str1,");
			strSql.Append("attr_str2=@attr_str2,");
			strSql.Append("attr_num1=@attr_num1");
			strSql.Append(" where id=@id");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@rank_id", DbType.Int32,8),
					new SQLiteParameter("@user_name", DbType.String),
					new SQLiteParameter("@user_pwd", DbType.String),
					new SQLiteParameter("@figure_url", DbType.String),
					new SQLiteParameter("@nickname", DbType.String),
					new SQLiteParameter("@sex", DbType.String),
					new SQLiteParameter("@birthday", DbType.String),
					new SQLiteParameter("@tel", DbType.String),
					new SQLiteParameter("@phone", DbType.String),
					new SQLiteParameter("@qq", DbType.String),
					new SQLiteParameter("@email", DbType.String),
					new SQLiteParameter("@province", DbType.String),
					new SQLiteParameter("@city", DbType.String),
					new SQLiteParameter("@area", DbType.String),
					new SQLiteParameter("@address", DbType.String),
					new SQLiteParameter("@user_type", DbType.Int32,8),
					new SQLiteParameter("@user_level", DbType.Int32,8),
					new SQLiteParameter("@qq_openid", DbType.String),
					new SQLiteParameter("@points", DbType.Int32,8),
					new SQLiteParameter("@status", DbType.Int32,8),
					new SQLiteParameter("@last_time", DbType.String),
					new SQLiteParameter("@this_time", DbType.String),
					new SQLiteParameter("@last_ip", DbType.String),
					new SQLiteParameter("@this_ip", DbType.String),
					new SQLiteParameter("@reg_time", DbType.String),
					new SQLiteParameter("@reset_key", DbType.String),
					new SQLiteParameter("@reset_expires", DbType.String),
					new SQLiteParameter("@remark", DbType.String),
					new SQLiteParameter("@attr_str1", DbType.String),
					new SQLiteParameter("@attr_str2", DbType.String),
					new SQLiteParameter("@attr_num1", DbType.Int32,8),
					new SQLiteParameter("@id", DbType.Int32,8)};
			parameters[0].Value = model.rank_id;
			parameters[1].Value = model.user_name;
			parameters[2].Value = model.user_pwd;
			parameters[3].Value = model.figure_url;
			parameters[4].Value = model.nickname;
			parameters[5].Value = model.sex;
			parameters[6].Value = model.birthday;
			parameters[7].Value = model.tel;
			parameters[8].Value = model.phone;
			parameters[9].Value = model.qq;
			parameters[10].Value = model.email;
			parameters[11].Value = model.province;
			parameters[12].Value = model.city;
			parameters[13].Value = model.area;
			parameters[14].Value = model.address;
			parameters[15].Value = model.user_type;
			parameters[16].Value = model.user_level;
			parameters[17].Value = model.qq_openid;
			parameters[18].Value = model.points;
			parameters[19].Value = model.status;
			parameters[20].Value = model.last_time;
			parameters[21].Value = model.this_time;
			parameters[22].Value = model.last_ip;
			parameters[23].Value = model.this_ip;
			parameters[24].Value = model.reg_time;
			parameters[25].Value = model.reset_key;
			parameters[26].Value = model.reset_expires;
			parameters[27].Value = model.remark;
			parameters[28].Value = model.attr_str1;
			parameters[29].Value = model.attr_str2;
			parameters[30].Value = model.attr_num1;
			parameters[31].Value = model.id;

			int rows=DbHelperSQLite.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from he_member ");
			strSql.Append(" where id=@id");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@id", DbType.Int32,4)
			};
			parameters[0].Value = id;

			int rows=DbHelperSQLite.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string idlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from he_member ");
			strSql.Append(" where id in ("+idlist + ")  ");
			int rows=DbHelperSQLite.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public He.Model.member GetModel(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,rank_id,user_name,user_pwd,figure_url,nickname,sex,birthday,tel,phone,qq,email,province,city,area,address,user_type,user_level,qq_openid,points,status,last_time,this_time,last_ip,this_ip,reg_time,reset_key,reset_expires,remark,attr_str1,attr_str2,attr_num1 from he_member ");
			strSql.Append(" where id=@id");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@id", DbType.Int32,4)
			};
			parameters[0].Value = id;

			He.Model.member model=new He.Model.member();
			DataSet ds=DbHelperSQLite.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public He.Model.member DataRowToModel(DataRow row)
		{
			He.Model.member model=new He.Model.member();
			if (row != null)
			{
				if(row["id"]!=null && row["id"].ToString()!="")
				{
					model.id=int.Parse(row["id"].ToString());
				}
				if(row["rank_id"]!=null && row["rank_id"].ToString()!="")
				{
					model.rank_id=int.Parse(row["rank_id"].ToString());
				}
				if(row["user_name"]!=null)
				{
					model.user_name=row["user_name"].ToString();
				}
				if(row["user_pwd"]!=null)
				{
					model.user_pwd=row["user_pwd"].ToString();
				}
				if(row["figure_url"]!=null)
				{
					model.figure_url=row["figure_url"].ToString();
				}
				if(row["nickname"]!=null)
				{
					model.nickname=row["nickname"].ToString();
				}
				if(row["sex"]!=null)
				{
					model.sex=row["sex"].ToString();
				}
				if(row["birthday"]!=null)
				{
					model.birthday=row["birthday"].ToString();
				}
				if(row["tel"]!=null)
				{
					model.tel=row["tel"].ToString();
				}
				if(row["phone"]!=null)
				{
					model.phone=row["phone"].ToString();
				}
				if(row["qq"]!=null)
				{
					model.qq=row["qq"].ToString();
				}
				if(row["email"]!=null)
				{
					model.email=row["email"].ToString();
				}
				if(row["province"]!=null)
				{
					model.province=row["province"].ToString();
				}
				if(row["city"]!=null)
				{
					model.city=row["city"].ToString();
				}
				if(row["area"]!=null)
				{
					model.area=row["area"].ToString();
				}
				if(row["address"]!=null)
				{
					model.address=row["address"].ToString();
				}
				if(row["user_type"]!=null && row["user_type"].ToString()!="")
				{
					model.user_type=int.Parse(row["user_type"].ToString());
				}
				if(row["user_level"]!=null && row["user_level"].ToString()!="")
				{
					model.user_level=int.Parse(row["user_level"].ToString());
				}
				if(row["qq_openid"]!=null)
				{
					model.qq_openid=row["qq_openid"].ToString();
				}
				if(row["points"]!=null && row["points"].ToString()!="")
				{
					model.points=int.Parse(row["points"].ToString());
				}
				if(row["status"]!=null && row["status"].ToString()!="")
				{
					model.status=int.Parse(row["status"].ToString());
				}
				if(row["last_time"]!=null)
				{
					model.last_time=row["last_time"].ToString();
				}
				if(row["this_time"]!=null)
				{
					model.this_time=row["this_time"].ToString();
				}
				if(row["last_ip"]!=null)
				{
					model.last_ip=row["last_ip"].ToString();
				}
				if(row["this_ip"]!=null)
				{
					model.this_ip=row["this_ip"].ToString();
				}
				if(row["reg_time"]!=null)
				{
					model.reg_time=row["reg_time"].ToString();
				}
				if(row["reset_key"]!=null)
				{
					model.reset_key=row["reset_key"].ToString();
				}
				if(row["reset_expires"]!=null)
				{
					model.reset_expires=row["reset_expires"].ToString();
				}
				if(row["remark"]!=null)
				{
					model.remark=row["remark"].ToString();
				}
				if(row["attr_str1"]!=null)
				{
					model.attr_str1=row["attr_str1"].ToString();
				}
				if(row["attr_str2"]!=null)
				{
					model.attr_str2=row["attr_str2"].ToString();
				}
				if(row["attr_num1"]!=null && row["attr_num1"].ToString()!="")
				{
					model.attr_num1=int.Parse(row["attr_num1"].ToString());
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,rank_id,user_name,user_pwd,figure_url,nickname,sex,birthday,tel,phone,qq,email,province,city,area,address,user_type,user_level,qq_openid,points,status,last_time,this_time,last_ip,this_ip,reg_time,reset_key,reset_expires,remark,attr_str1,attr_str2,attr_num1 ");
			strSql.Append(" FROM he_member ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQLite.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM he_member ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
            object obj = DbHelperSQLite.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.id desc");
			}
			strSql.Append(")AS Row, T.*  from he_member T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQLite.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@tblName", DbType.VarChar, 255),
					new SQLiteParameter("@fldName", DbType.VarChar, 255),
					new SQLiteParameter("@PageSize", DbType.Int32),
					new SQLiteParameter("@PageIndex", DbType.Int32),
					new SQLiteParameter("@IsReCount", DbType.bit),
					new SQLiteParameter("@OrderType", DbType.bit),
					new SQLiteParameter("@strWhere", DbType.VarChar,1000),
					};
			parameters[0].Value = "he_member";
			parameters[1].Value = "id";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQLite.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  BasicMethod
		#region  ExtensionMethod

		#endregion  ExtensionMethod
	}
}

